package org.shean.app.rssreader.provider;

import java.util.Calendar;

import org.shean.app.rssreader.utils.DateUtils;

import android.content.ContentProvider;
import android.content.ContentUris;
import android.content.ContentValues;
import android.content.Context;
import android.content.UriMatcher;
import android.content.res.Resources;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteQueryBuilder;
import android.net.Uri;
import android.text.TextUtils;
import android.util.Log;

public class SzRSSProvider extends ContentProvider {

	private static final String DATABASE_NAME = "mydb.sqlite";
	private static final int DATABASE_VERSION = 15;

	private DatabaseHelper mHelper;

	private static final int CHANNELS = 1;
	private static final int CHANNEL_ID = 2;
	private static final int POSTS = 3;
	private static final int POST_ID = 4;
	private static final int CHANNEL_POSTS = 5;
	private static final int POST_TAG_ID = 6;
	private static final int POST_TAGS = 7;
	private static final int TAGS = 8;
	private static final int POST_COMMENT = 9;
	private static final int TAG_ID = 10;	
	
	private static final UriMatcher URL_MATCHER;

	private static class DatabaseHelper extends SQLiteOpenHelper {
		public DatabaseHelper(Context ctx) {
			super(ctx, DATABASE_NAME, null, DATABASE_VERSION);
		}

		@Override
		public void onCreate(SQLiteDatabase db) {
			// create channel database
			db
					.execSQL("CREATE TABLE szchannel ("
							+ "_id INTEGER PRIMARY KEY, " + "title TEXT, "
							+ "url TEXT UNIQUE, " + "link TEXT, "
							+ "description TEXT, " + "language TEXT, "
							+ "lastBuildDate DATETIME, "
							+ "lastRefreshDate DATETIME);");

			// create post database
			db.execSQL("CREATE TABLE szpost (" + "_id INTEGER PRIMARY KEY, "
					+ "channel_id INTEGER, " + "title TEXT, " + "link TEXT, "
					+ "description TEXT, " + "postedOnDate DATETIME, "
					+ "isRead INTEGER(1) DEFAULT '0', "
					+ "isFavorite INTEGER(1) DEFAULT '0');");

			// create tag database
			db.execSQL("CREATE TABLE sztag (" + "_id INTEGER PRIMARY KEY, "
					+ "name TEXT);");

			// create tag-post relation database
			db.execSQL("CREATE TABLE szposttag (" + "_id INTEGER PRIMARY KEY, "
					+ "tag_id INTEGER, " 
					+ "channel_id INTEGER, "
					+ "post_id INTEGER);");
			
			db.execSQL("CREATE TABLE szpostcomment (" + "_id INTEGER PRIMARY KEY, "
					+ "comment TEXT, " 
					+ "channel_id INTEGER, " 
					+ "create_date DATETIME, "
					+ "post_id INTEGER);");
			
			db.execSQL("CREATE VIEW IF NOT EXISTS szposttag_info AS " +
                    "SELECT szposttag._id AS _id, channel_id, post_id, tag_id, " +
                    "sztag.name AS name" +
                    " FROM szposttag LEFT OUTER JOIN sztag ON szposttag.tag_id=sztag._id" +
                    " GROUP BY szposttag._id;");
			
			db.execSQL("CREATE VIEW IF NOT EXISTS szpost_info AS " +
                    "SELECT szposttag._id AS _id, szpost.title AS title, szpost.channel_id AS channel_id, " +
                    "szpost.isRead AS isRead, szpost.postedOnDate AS postedOnDate, szpost.isFavorite AS isFavorite, " +
                    "tag_id, szpost._id AS post_id " +
                    " FROM szposttag LEFT OUTER JOIN szpost ON szpost._id=szposttag.post_id" +
                    " GROUP BY szposttag._id;");
			
			db.execSQL("CREATE TRIGGER IF NOT EXISTS tag_cleanup DELETE ON szpost " +
                    "BEGIN " +
                        "DELETE FROM szposttag WHERE post_id = old._id;" +
                    "END");
			
			db.execSQL("CREATE TRIGGER IF NOT EXISTS comment_cleanup DELETE ON szpost " +
                    "BEGIN " +
                        "DELETE FROM szpostcomment WHERE post_id = old._id;" +
                    "END");
			db.execSQL("CREATE TRIGGER IF NOT EXISTS tag_cleanup2 DELETE ON sztag " +
                    "BEGIN " +
                        "DELETE FROM szposttag WHERE tag_id = old._id;" +
                    "END");
			
		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			switch (oldVersion) {
			default:
				db.execSQL("DROP TABLE IF EXISTS szchannel;");
				db.execSQL("DROP TABLE IF EXISTS szpost;");
				db.execSQL("DROP TABLE IF EXISTS sztag;");
				db.execSQL("DROP TABLE IF EXISTS szposttag;");
				db.execSQL("DROP TABLE IF EXISTS szpostcomment;");
				db.execSQL("DROP VIEW IF EXISTS szposttag_info;");
				db.execSQL("DROP VIEW IF EXISTS szpost_info;");
				db.execSQL("DROP TRIGGER IF EXISTS tag_cleanup");
				db.execSQL("DROP TRIGGER IF EXISTS comment_cleanup");
				db.execSQL("DROP TRIGGER IF EXISTS tag_cleanup2");
				onCreate(db);
				break;
			}
		}
	}

	@Override
	public int delete(Uri url, String where, String[] whereArgs) {
		SQLiteDatabase db = mHelper.getWritableDatabase();
		int count;
		String myWhere;
		
		switch (URL_MATCHER.match(url))
		{
		case CHANNELS:
			count = db.delete("szchannel", where, whereArgs);
			break;
			
		case CHANNEL_ID:
			myWhere = "_id=" + url.getPathSegments().get(1) + (!TextUtils.isEmpty(where) ? " AND (" + where + ")" : "");			
			count = db.delete("szchannel", myWhere, whereArgs);
			break;
			
		case POSTS:
			count = db.delete("szpost", where, whereArgs);
			break;
			
		case POST_ID:
			myWhere = "_id=" + url.getPathSegments().get(1) + (!TextUtils.isEmpty(where) ? " AND (" + where + ")" : "");			
			count = db.delete("szpost", myWhere, whereArgs);
			break;
		case POST_TAGS:
			count = db.delete("szposttag", where, whereArgs);
			break;
		case POST_COMMENT:
			count = db.delete("szpostcomment", where, whereArgs);	
			break;
		case TAGS:
			count = db.delete("sztag", where, whereArgs);
			break;
		case TAG_ID:
			myWhere = "_id=" + url.getPathSegments().get(1) + (!TextUtils.isEmpty(where) ? " AND (" + where + ")" : "");
			count = db.delete("sztag", myWhere, whereArgs);
			break;
		default:
			throw new IllegalArgumentException("Unknown URL " + url);
		}
		
		getContext().getContentResolver().notifyChange(url, null);
		return count;
	}

	@Override
	public String getType(Uri uri) {
		switch (URL_MATCHER.match(uri)) {
		case CHANNELS:
			return "vnd.android.cursor.dir/vnd.szrssreader.channel";
		case CHANNEL_ID:
			return "vnd.android.cursor.item/vnd.szrssreader.channel";
		case POSTS:
		case CHANNEL_POSTS:
			return "vnd.android.cursor.dir/vnd.szrssreader.post";
		case POST_ID:
			return "vnd.android.cursor.item/vnd.szrssreader.post";
		case POST_TAG_ID:
			return "vnd.android.cursor.item/vnd.szrssreader.posttag";
		case POST_TAGS:
			return "vnd.android.cursor.dir/vnd.szrssreader.posttag";
		case TAGS:
			return "vnd.android.cursor.dir/vnd.szrssreader.tag";
		case TAG_ID:
			return "vnd.android.cursor.item/vnd.szrssreader.tag";
		case POST_COMMENT:
			return "vnd.android.cursor.dir/vnd.szrssreader.postcomment";
		default:
			throw new IllegalArgumentException("Unknown URL " + uri);
		}
	}

	@Override
	public Uri insert(Uri uri, ContentValues initialValues) {
		SQLiteDatabase db = mHelper.getWritableDatabase();
		long rowID;
		ContentValues values;

		if (initialValues != null)
			values = new ContentValues(initialValues);
		else
			values = new ContentValues();
		
		Uri res;
		
		if (URL_MATCHER.match(uri) == CHANNELS)
		{
			rowID = insertChannels(db, values);
			res = ContentUris.withAppendedId(SzRSSReader.Channels.CONTENT_URI, rowID);
		}
		else if (URL_MATCHER.match(uri) == POSTS)
		{
			rowID = insertPosts(db, values);
			res = ContentUris.withAppendedId(SzRSSReader.Posts.CONTENT_URI, rowID);
		}
		else if(URL_MATCHER.match(uri) == POST_TAGS)
		{
			rowID = insertTags(db, values);
			res = ContentUris.withAppendedId(SzRSSReader.PostTags.CONTENT_URI, rowID);
		}
		else if(URL_MATCHER.match(uri) == POST_COMMENT)
		{
			rowID =  insertComments(db, values);
			res = ContentUris.withAppendedId(SzRSSReader.PostComments.CONTENT_URI, rowID);
		}
		else
		{
			throw new IllegalArgumentException("Unknown URL " + uri);
		}
		
		if (rowID > 0)
		{
			assert(res != null);
			getContext().getContentResolver().notifyChange(res, null);
			return res;
		}
		
		throw new SQLException("Failed to insert row into " + uri);
	}
	
	private long insertComments(SQLiteDatabase db, ContentValues values)
	{
		String post_id = values.getAsString(SzRSSReader.PostComments.POST_ID);
		String comment = values.getAsString(SzRSSReader.PostComments.COMMENT);
		if(comment == null || post_id == null) return -1;
		
		
		Cursor c1 = db.query("szpost", new String[]{SzRSSReader.Posts.CHANNEL_ID}, SzRSSReader.Posts._ID + "='" + post_id +"'" , 
				null, null, null, null);
		
		if(c1.getCount() == 0)
		{
			c1.close();
			return -1;
		}
		
		c1.moveToFirst();
		long channel_id = c1.getLong(c1.getColumnIndexOrThrow(SzRSSReader.Posts.CHANNEL_ID));
		c1.close();
	
		ContentValues newPostcommnet = new ContentValues();
		newPostcommnet.put(SzRSSReader.PostComments.POST_ID, post_id);
		newPostcommnet.put(SzRSSReader.PostComments.COMMENT, comment);
		newPostcommnet.put(SzRSSReader.PostComments.CHANNEL_ID, channel_id);
		newPostcommnet.put(SzRSSReader.PostComments.CREATE_DATE, DateUtils
				.formatDate(Calendar.getInstance().getTime()));
	
		return db.insert("szpostcomment", SzRSSReader.PostComments.POST_ID, newPostcommnet);
	}
	
	private long insertTags(SQLiteDatabase db, ContentValues values)
	{
		String post_id = values.getAsString(SzRSSReader.PostTags.POST_ID);
		String tag_name = values.getAsString(SzRSSReader.PostTags.NAME);
		if(tag_name == null || post_id == null) return -1;
		
		
		Cursor c1 = db.query("szpost", new String[]{SzRSSReader.Posts.CHANNEL_ID}, SzRSSReader.Posts._ID + "='" + post_id +"'" , 
				null, null, null, null);
		
		if(c1.getCount() == 0)
		{
			c1.close();
			return -1;
		}
		
		c1.moveToFirst();
		long channel_id = c1.getLong(c1.getColumnIndexOrThrow(SzRSSReader.Posts.CHANNEL_ID));
		c1.close();
		
		Cursor c = db.query("sztag", new String[]{SzRSSReader.Tags._ID}, SzRSSReader.Tags.NAME + "='" + tag_name +"'" , 
				null, null, null, null);
		
		long tag_id;
		
		if(c == null || c.getCount() == 0)
		{
			ContentValues newtag = new ContentValues();
			newtag.put(SzRSSReader.Tags.NAME, tag_name);
			tag_id = db.insert("sztag", SzRSSReader.Tags.NAME, newtag);
		}
		else
		{
			c.moveToFirst();
			tag_id = c.getLong(0);
		}
		c.close();
	
		ContentValues newPosttag = new ContentValues();
		newPosttag.put(SzRSSReader.PostTags.POST_ID, post_id);
		newPosttag.put("tag_id", tag_id);
		newPosttag.put(SzRSSReader.PostTags.CHANNEL_ID, channel_id);
		
		db.insert("szposttag", SzRSSReader.PostTags.POST_ID, newPosttag);
		return Long.parseLong(post_id);
	}
	
	private long insertChannels(SQLiteDatabase db, ContentValues values)
	{
		Resources r = Resources.getSystem();

		/* TODO: This validation sucks. */
		if (values.containsKey(SzRSSReader.Channels.TITLE) == false)
			values.put(SzRSSReader.Channels.TITLE, r.getString(android.R.string.untitled));

		long id = db.insert("szchannel", "title", values);
		return id;
	}
	
	private long insertPosts(SQLiteDatabase db, ContentValues values)
	{
		/* TODO: Validation? */
		return db.insert("szpost", "title", values);
	}


	@Override
	public boolean onCreate() {
		mHelper = new DatabaseHelper(getContext());
		return true;
	}

	@Override
	public Cursor query(Uri uri, String[] projection, String selection,
			String[] selectionArgs, String sortOrder) {
		SQLiteQueryBuilder qb = new SQLiteQueryBuilder();
		String defaultSort = null;

		switch (URL_MATCHER.match(uri)) {
		case CHANNELS:
			qb.setTables("szchannel");
			defaultSort = SzRSSReader.Channels.DEFAULT_SORT_ORDER;
			break;

		case CHANNEL_ID:
			qb.setTables("szchannel");
			qb.appendWhere("_id=" + uri.getPathSegments().get(1));
			break;
		case CHANNEL_POSTS:
			qb.setTables("szpost");
			qb.appendWhere("channel_id=" + uri.getPathSegments().get(1));
			defaultSort = SzRSSReader.Posts.DEFAULT_SORT_ORDER;
			break;
		case POSTS:
			qb.setTables("szpost");
			defaultSort =SzRSSReader.Posts.DEFAULT_SORT_ORDER;
			break;
		case POST_ID:
			qb.setTables("szpost");
			qb.appendWhere("_id=" + uri.getPathSegments().get(1));
			break;
		case POST_TAG_ID:
			qb.setTables("szposttag_info");
			qb.appendWhere("post_id=" + uri.getPathSegments().get(1));
			defaultSort = SzRSSReader.PostTags.DEFAULT_SORT_ORDER;
			break;
		case POST_TAGS:
			qb.setTables("szposttag_info");
			defaultSort = SzRSSReader.PostTags.DEFAULT_SORT_ORDER;
			break;
		case TAGS:
			qb.setTables("sztag");
			defaultSort = SzRSSReader.Tags.DEFAULT_SORT_ORDER;
			break;
		case TAG_ID:
			qb.setTables("szpost_info");
			qb.appendWhere("tag_id=" + uri.getPathSegments().get(1));
			defaultSort = SzRSSReader.Posts.DEFAULT_SORT_ORDER;
			break;
		case POST_COMMENT:
			qb.setTables("szpostcomment");
			defaultSort = SzRSSReader.PostComments.DEFAULT_SORT_ORDER;
			break;
		default:
			throw new IllegalArgumentException("Unknown URL " + uri);
		}

		String orderBy;

		if (TextUtils.isEmpty(sortOrder))
			orderBy = defaultSort;
		else
			orderBy = sortOrder;

		SQLiteDatabase db = mHelper.getReadableDatabase();
		Cursor c = qb.query(db, projection, selection, selectionArgs, null,
				null, orderBy);

		c.setNotificationUri(getContext().getContentResolver(), uri);

		return c;
	}

	@Override
	public int update(Uri url, ContentValues values, String where,
			String[] whereArgs) {
		SQLiteDatabase db = mHelper.getWritableDatabase();
		int count;
		String myWhere;
		
		switch (URL_MATCHER.match(url))
		{
		case CHANNELS:
			count = db.update("channel", values, where, whereArgs);
			break;
			
		case CHANNEL_ID:
			myWhere = "_id=" + url.getPathSegments().get(1) + (!TextUtils.isEmpty(where) ? " AND (" + where + ")" : "");			
			count = db.update("szchannel", values, myWhere, whereArgs);
			break;
			
		case POSTS:
			count = db.update("szpost", values, where, whereArgs);
			break;
			
		case POST_ID:
			myWhere = "_id=" + url.getPathSegments().get(1) + (!TextUtils.isEmpty(where) ? " AND (" + where + ")" : "");			
			count = db.update("szpost", values, myWhere, whereArgs);
			break;
		case CHANNEL_POSTS:
			myWhere = "channel_id=" + url.getPathSegments().get(1) + (!TextUtils.isEmpty(where) ? " AND (" + where + ")" : "");
			count = db.update("szpost", values, myWhere, whereArgs);
			break;
		case POST_COMMENT:
			count = db.update("szpostcomment", values, where, whereArgs);
			break;
		default:
			throw new IllegalArgumentException("Unknown URL " + url);
		}
		
		getContext().getContentResolver().notifyChange(url, null);
		return count;
	}

	static {
		URL_MATCHER = new UriMatcher(UriMatcher.NO_MATCH);
		URL_MATCHER.addURI(SzRSSReader.AUTHORITY, "channels", CHANNELS);
		URL_MATCHER.addURI(SzRSSReader.AUTHORITY, "channels/#", CHANNEL_ID);
		URL_MATCHER.addURI(SzRSSReader.AUTHORITY, "posts", POSTS);
		URL_MATCHER.addURI(SzRSSReader.AUTHORITY, "posts/#", POST_ID);

		/* TODO: use channels/#/posts */
		URL_MATCHER.addURI(SzRSSReader.AUTHORITY, "postlist/#", CHANNEL_POSTS);
		URL_MATCHER.addURI(SzRSSReader.AUTHORITY, "postlist/-1", CHANNEL_POSTS);
		
		URL_MATCHER.addURI(SzRSSReader.AUTHORITY, "posttags/#", POST_TAG_ID);
		URL_MATCHER.addURI(SzRSSReader.AUTHORITY, "posttags", POST_TAGS);
		URL_MATCHER.addURI(SzRSSReader.AUTHORITY, "tags", TAGS);
		URL_MATCHER.addURI(SzRSSReader.AUTHORITY, "tags/#", TAG_ID);
		URL_MATCHER.addURI(SzRSSReader.AUTHORITY, "postcomments", POST_COMMENT);
		
		
	}

}
